package cn.pconline.autoclub.task.data;

import cn.pconline.autoclub.task.AppUtils;
import cn.pconline.autoclub.task.DBUtils;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @email chensy0203@163.com
 * @author chensy
 */
public class InitUserForum {

    private static final Logger log = LoggerFactory.getLogger(InitUserForum.class);
    static List<String> userForumColumns = null;
    private static final long specialFid = 21245;
    private static final String specialFname = "版主俱乐部";
    private static final String specialUid = "15527, 21402, 60332, 63145, 76798, 156020, 165926, 249553, 337845, 373785, 405942, 437194, 584590, 614569, 646112, 715366, 826717, 877830, 989601, 1318905, 1749594, 1975288, 2746602, 2747345, 2915652, 2943737, 2952136, 3016845, 3098579, 3099795, 3135429, 3735318, 3987712, 4085857, 4181046, 4251760, 4323162, 4390970, 4420245, 4527736, 4584300, 4781621, 4816843, 4920763, 4925248, 4954057, 5102658, 5142323, 5171599, 5176607, 5191448, 5229178, 5501834, 5559998, 5582915, 5609632, 5652236, 5747391, 5793527, 5935540, 5962011, 6014902, 6023150, 6112860, 6157378, 6203036, 6261187, 6332239, 6427491, 6455366, 6465312, 6629786, 6638857, 6671181, 6746144, 6752051, 6761430, 6765601, 6784601, 6946775, 6955986, 7097361, 7114954, 7127189, 7258584, 7391533, 7575093, 7594769, 7743049, 7889205, 7924459, 8107860, 8125250, 8129818, 8217677, 8256894, 8399170, 8640153, 8665295, 8803955, 8900728, 8917973, 9006421, 9022457, 9075484, 9094854, 9112159, 9129825, 9271155, 9291710, 9311789, 9385864, 9414732, 9418902, 9448202, 9533591, 9721133, 9804772, 10033392, 10043084, 10047965, 10185819, 10525538, 10527687, 10589217, 10699148, 11103544, 11154945, 13128876, 14004139, 14012529, 14197396, 14217830, 14350911, 14690043, 14804196, 14898823, 15221422, 15503474, 15790639, 15959452, 16010991, 16080689, 16126473, 16413305, 16428865, 16564263, 16581077, 16656115, 16752851, 16777347, 16795847, 16855828, 16878990, 16963668, 17488730, 18153313, 20032385, 20085701, 20160810, 20175803, 20181072, 20181423, 20191567, 20191568, 20191575, 20191592, 20191625, 20191634, 20191642, 20191643, 20191751, 20191789, 20191833, 20191834, 20191890, 20191940, 20191960, 20191968, 20191970, 20191982, 20192226, 20192227, 20192389, 20192421, 20192425, 20192434, 20192514, 20192742, 20192752, 20192794, 20192877, 20192912, 20192996, 20195822, 20215268, 20236969, 20290647, 20311538, 20326558, 20329087, 20329726, 20346644, 20365751, 20409696, 20428592, 20505737, 20515621, 20560251, 20652492, 20742731, 20744835, 20767525, 20803846, 20873506, 20895683, 20940911, 21066210, 21207769, 21288270, 21295312, 21487658, 21487679, 21518775, 21672744, 21689415, 21699311, 21709041, 22173189, 22248160, 22310588, 22316904, 22330104, 22387382, 22416134, 22427952, 22600977, 22627382, 22703006, 22717621, 22757128, 22759797, 22827357, 22938614, 22944064, 22957364, 22966643, 23035619, 23048876, 23114995, 23298322, 23319635, 23479474, 23527158, 23532144, 23643649, 23659093, 23664202, 23740742, 23745983, 23764781, 23841559, 23985296, 24031375, 24098632, 24169033, 24179651, 24202344, 24241614, 24337276, 24369233, 24409739, 24420824, 24437826, 24510949, 24573236, 24574411, 24625325, 24654370, 24696329, 24802233, 24856058, 24924279, 25032608, 25055665, 25172600, 25180941, 25197451, 25197503, 25197526, 25208498, 25216242, 25217603, 25388526, 25402264, 25450045, 25490674, 25502107, 25518957, 25575778, 25589331, 25644138, 25644146, 25647136, 25740999, 25741052, 25741057, 25795218, 25815471, 25857887, 25869936, 25890524, 25929300, 25945617, 25956023, 25974632, 25996394, 26043150, 26086969, 26195133, 26202692, 26214295, 26269523, 26346165, 26504592, 26504610, 26505165, 26505379, 26505651, 26506602, 26507037, 26507205, 26507271, 26507317, 26507343, 26507378, 26507415, 26507434, 26560233, 26568056, 26586954, 26598136, 26616707, 26729335, 26737428, 26839875, 26905949, 26980263, 27002976, 27035793, 27067101, 27070845, 27070914, 27070983, 27071237, 27071329, 27071347, 27071366, 27071396, 27071433, 27071460, 27071479, 27071493, 27071504, 27071526, 27111262, 27161836, 27183675, 27215793, 27230280, 27262347, 27267952, 27350633, 27377471, 27379949, 27498940, 27507985, 27547030, 27574958, 27576767, 27589081, 27617992, 27623492, 27663792, 27680499, 27808162, 27810913, 27830334, 27832356, 27864536, 27866750, 27887124, 27887186, 27904838, 27923163, 27924193, 27964065, 28075804, 28086483, 28094864, 28096546, 28140727, 28143617, 28303517, 28321599, 28343014, 28394169, 28398169, 28403747, 28436533, 28441422, 28449380, 28457131, 28475173, 28475209, 28477524, 28494299, 28500647, 28505107, 28527968, 28563800, 28573254, 28577977, 28586293, 28589809, 28632869, 28635997, 28725185, 28765288, 28777974, 28785771, 28785832, 28785835, 28785865, 28787104, 28841389, 28845945, 28981398, 28991156, 28995295, 29059541, 29059798, 29059848, 29060056, 29060301, 29107833, 29108516, 29109449, 29274334, 29275651, 29282056, 29316194, 29316197, 29325763, 29336927, 29337324, 29350716, 29538070, 29569186, 29578020, 29588791, 29590584, 29648760, 29675951, 29675988, 29676026, 29728374, 29745068, 29750845, 29752244, 29755863, 29768515, 29797603, 29805748, 29805834, 29805878, 29866209, 29877207, 29893073, 29927130, 29965166, 29977745, 30036829, 30067059, 30072207, 30151526, 30170982, 30184274, 30198846, 30238677, 30240461, 30240549, 30243004, 30243281, 30243386, 30256291, 30350393, 30461382, 30469301, 30469807, 30478218, 30498273, 30506963, 30545229, 30580749, 30632287, 30632676, 30633988, 30658646, 30669136, 30679499, 30692125, 30828579, 30848104, 30860217, 30885391, 30911967, 30913265, 30938132, 30955141, 30968648, 30994000, 30994918, 31000498, 31012178, 31019867, 31053283, 31128893, 31144101, 31147358, 31171948, 31172924, 31177543, 31182785, 31190234, 31198577, 31199381, 31200545, 31212899, 31224699, 31236512, 31248165, 31249733, 31250527, 31256969, 31265718, 31278910, 31296899, 31312997, 31315663, 31327871, 31348669, 31362308, 31393200, 31408959, 31421915, 31426666, 31431657, 31431987, 31476853, 31477770, 31671818, 31705377, 31792200, 31830754, 31862112, 31888877, 31918733, 31929065, 31932281, 31949699, 31972686, 32004594, 32032207, 32135625, 32287255, 32290205, 32299071, 32304777, 32358623, 32368386, 32384711, 32411268, 32426912, 32438669, 32438946, 32479291, 32489989, 32493544, 32524603, 32537378, 32548328, 32570863, 32573568, 32581460, 32581505, 32608111, 32611226, 32612937, 32613133, 32624054, 32624120, 32624150, 32624232, 32626785, 32626988, 32627209, 32627604, 32627858, 32628427, 32630900, 32630976, 32631521, 32635331, 32680899, 32682613, 32686359, 32693454, 32698757, 32702883, 32717481, 32731912, 32785434, 32859344, 32870587, 32879965, 32883730, 32883972, 32903977, 32946374, 32946451, 32999039, 33009974, 33033692, 33036940, 33037128, 33046766, 33091468, 33100062, 33102203, 33129615, 33169417, 33177768, 33215631, 33241272, 33309220, 33330006, 33342186, 33352067, 33358656, 33383822, 33384000, 33385439, 33390353, 33397497, 33406446, 33412331, 33415651, 33419643, 33419964, 33420999, 33422306, 33435311, 33440785, 33441927, 33446477, 33456810, 33488787, 33491127, 33491529, 33492831, 33501700, 33517383, 33534061, 33536720, 33543838, 33569543, 33576741, 33580105, 33580158, 33593537, 33595682, 33602922, 33610134, 33627693, 33632013, 33632804, 33641431, 33644531, 33656898, 33658381, 33661162, 33669640, 33685708, 33690637, 33699978, 33705107, 33721183, 33746879, 33751445, 33771575, 33799013, 33820588, 33837433, 33838916, 33842985, 33870373, 33881663, 33884898, 33889136, 33890308, 33891857, 33893935, 33914620, 33914690, 33935824, 33944862, 33948825, 33952359, 33952679, 33953422, 33955336, 33959674, 33967680, 33968303, 34010290, 34107339, 34107778, 34140485, 34149558, 34150060, 34150814, 34176898, 34202424, 34206308, 34214219, 34228857, 34236584, 34257041, 34284850, 34288378, 34290670, 34293531, 34293709, 34315091, 34319414, 34320189, 34320708, 34325355, 34329333, 34329357, 34358229, 34365774, 34379632, 34386774, 34435510, 34435550, 34451527, 34451666, 34468499, 34476426, 34480136, 34481059, 34497881, 34502449, 34551692, 34567089, 34574692, 34579169, 34621681, 34623975, 34629962, 34629972, 34630373, 34632486, 34638522, 34639375, 34640881, 34642312, 34646056, 34648011, 34673232, 34696533, 34696586, 34705626, 34732370, 34752373, 34759687, 34761679, 34825172, 34827589, 34833858, 34834238, 34849128, 34856222, 34858557, 34863640, 34882443, 34882638, 34903082, 34915447, 34922923, 34938256, 34952218, 34968741, 34971291, 34985642, 35000891, 35010358, 35013313, 35031024, 35032645, 35042107, 35068014, 35078758, 35083346, 35100775, 35154573, 35155478, 35162248, 35180596, 35180928, 35236829, 35289707, 35292292, 35319567, 35451150, 35459212, 35472063, 35496162, 35533594, 35542218, 35580871, 35582205, 35602396, 35612264, 35655217, 35725159, 35745790, 35760356, 35924419, 36145281, 36187758, 36227073,";
    private static final String brandIdNameStr = "582;宝骏,583;海马郑州,764;沃克斯豪尔,603;Smart,623;纳智捷,633;启辰,643;北汽威旺,653;路特斯,663;劳伦士,570;科尼赛克,571;广汽传祺,578;开瑞,579;威麟,580;瑞麒,581;东风风神,567;光冈,568;九龙,574;Rossion,46;莲花,83;金杯,53;双龙,8;海马,4;奔驰,16;东南,75;昌河,26;捷豹,21;福特,111;东风,396;红旗,365;荣威,39;克莱斯勒,10;丰田,2;大众,62;阿斯顿马丁,41;标致,72;道奇,101;江铃,119;双环,122;北汽制造,195;广汽吉奥,316;玛莎拉蒂,306;奔腾,265;GMC,69;斯柯达,18;菲亚特,47;劳斯莱斯,305;力帆,70;凯迪拉克,140;讴歌,78;江淮,81;华普,28;英菲尼迪,49;斯巴鲁,104;中华,115;华泰,58;猎豹汽车,124;长安,38;Jeep,355;金龙汽车,205;MINI,546;世爵,154;西雅特,63;布加迪,44;保时捷,24;沃尔沃,110;长城,34;现代,225;雪佛兰,506;中欧,9;一汽,17;马自达,20;宝马,59;悍马,23;萨博,125;中兴,307;众泰,325;中顺,516;野马汽车,6;雪铁龙,7;别克,3;本田,73;铃木,133;黄海,57;奇瑞,29;路虎,30;雷克萨斯,45;宾利,40;雷诺,82;哈飞,103;福田,132;依维柯,120;黑豹,116;福迪,387;迈巴赫,235;大迪,275;永源,345;MG名爵,61;法拉利,15;日产,60;阿尔法罗密欧,12;起亚,13;吉利汽车,1;奥迪,64;兰博基尼,32;三菱,107;比亚迪,66;林肯,114;金旅,22;欧宝,118;五菱,569;陆风,573;帕加尼,604;理念,593;北京,613;长安商用,673;上汽大通MAXUS,723;巴博斯,753;威兹曼,754;DS,844;朗世,855;恒天,733;思铭,834;FM Auto,683;其他品牌,704;卡尔森,743;腾势,794;Fisker,824;观致,845;哈弗,703;欧朗,774;特斯拉,814;北汽绅宝,876;海格,693;AC宝马,715;迈凯伦,804;佳跃,856;东风小康,866;之诺,878;福汽启腾,898;北汽幻速,908;摩根,918;五十铃,919;英致,929;知豆,959;AMG,969;泰卡特,990;成功,1001;华颂,1011;领志,1012;卡威汽车,1022;雷丁,877;东风风度,888;KTM,939;陆地方舟,949;东风风行,950;北汽新能源,970;凯翼,980;赛麟,991;科瑞斯的,1032;Noble,1033;nanoFLOWCELL,1043;Zenvo,1053;南京金龙,1063;御捷,1064;W Motors,1074;前途,1075;凯马";
    private static final String specialSerialIdNameStr = "3;奥迪A2,5;奥迪A4,6;奥迪A6,7;奥迪A8L,8;奥迪TT,11;思域(进口),12;雅阁(进口),13;本田CR-V(进口),14;奥德赛(进口),22;英菲尼迪QX,23;奥拓,25;羚羊,32;揽胜,33;新主席,34;雷克萨斯IS,35;柯兰多Korando,36;雷克萨斯ES,38;雷克萨斯GS,39;雷克萨斯SC,40;悍马H1,41;雷克萨斯LS,42;雷克萨斯RX,43;雷克萨斯LX,46;丰田Corolla,47;法拉利F430,52;速派,54;欧雅,56;丰田RAV4(进口),63;丰田Land Cruiser,64;普瑞维亚,66;伊柯丽斯,74;甲壳虫,75;领航员,77;大众Passat,78;欧陆,79;大众Jetta,82;酷派,83;索纳塔(进口),84;林肯LS,89;捷达,90;雅阁,98;大众Polo,99;帕萨特,100;桑塔纳·尚纳,101;富康,102;萨拉毕加索,106;别克GL8,111;V8 Vantage,112;Vanquish,113;威航,118;蝙蝠Murcielago,120;美人豹,121;英伦C5,122;豪情,126;派力奥,129;蓝鸟,132;富利卡,138;奔驰G级,139;奔驰M级,140;凯领,141;奔驰C级(进口),142;奔驰A级,144;奔驰E级(进口),146;奔驰SL,147;奔驰SLK,148;道奇Durango,154;牧马人,155;大切诺基,157;拉古那,158;道奇Ram,160;宝马X6 M,161;宝马3系(进口),163;标致607,164;宝马7系,167;宝马5系(进口),169;宝马X5,172;保时捷911,176;Boxster,177;蝰蛇,178;君威(进口),182;雅致,186;路特斯Esprit,188;超级维特拉,205;威达,206;雅特,207;PT漫步者,210;赛飞利,213;萨博9-5,214;赛马,215;瑞风,217;中意,219;沃尔沃S80,220;沃尔沃S60,221;沃尔沃C70,222;沃尔沃V70,223;捷豹XK,224;捷豹S-TYPE,225;捷豹XJ,228;福克斯(进口),231;金牛座(进口),232;野马,250;海狮,253;奥迪A3(进口),254;雪铁龙C5(进口),255;福特Escape,256;探险者,259;征服者Expedition,267;奔驰S级,292;全顺,294;福美来,302;金迪尔,303;赛铃,304;赛弗,307;帕拉丁,309;日产D22,311;梅甘娜,312;雷诺风景,317;标致206(进口),319;标致307,331;美佳,333;千里马,334;帕杰罗(进口),336;索纳塔八,341;威姿,343;高尔,349;卡宴,354;马自达6,356;神行者2,357;特拉卡,358;民意,359;路宝,363;赛纳,364;奇骏(进口),366;宝马Z4,367;奔驰CL,368;菱帅,377;阳光,378;五菱之光,379;来旺,380;东方之子,385;奔驰CLK,397;长安之星,399;旗舰,411;普拉多,414;马自达6(进口),415;赛影,418;陆铃,419;爱迪尔,420;沃尔沃XC90,423;沃尔沃XC70,424;宝马3系,434;特锐,441;宝马5系,443;挑战者SUV,444;飞腾,445;黑金刚,457;来宝,469;途乐,470;威乐,472;锐驰,487;花冠,491;奇瑞QQ3,498;讴歌NSX,499;宝马6系,500;宝马X3,503;标致407,506;本田NSX,509;讴歌TL,520;奔驰CLS,530;发现3,532;三菱Colt,543;现代Accent,544;伊兰特(进口),547;欧菲莱斯,548;起亚Picanto,549;起亚RIO,551;速迈,553;欧宝Corsa,554;麦瑞纳,560;凯迪拉克CTS,562;凯迪拉克XLR,566;路特斯Elise,568;雪铁龙C2(进口),574;嘉年华(进口),576;福特Fusion,577;福特GT,578;福特Ka,579;蒙迪欧(进口),580;捷豹F-TYPE,581;盖拉多Gallardo,585;马自达2,586;马自达3(进口),590;马自达RX-8,592;日产350Z,595;日产Micra,596;日产Pathfinder,600;雷诺Espace,602;雷诺Twingo,606;丰田Avalon,607;丰田Verso,622;普锐斯(进口),623;丰田Yaris,626;辉腾,627;大众Polo(进口),628;夏朗,631;本田CR-V,634;菲亚特Panda,635;菲亚特Doblo,642;菱绅,651;嘉华,652;陆霸,659;起亚Optima,678;途安,684;标致EX1,686;远舰,692;五菱扬光,744;海域,774;五菱兴旺,793;江南TT,823;普拉多(进口),865;尊驰,871;荣御,896;凯运,903;smart fortwo,905;ALFA 156,913;讴歌MDX,914;讴歌RL,919;DB9,933;皇冠,943;格蓝迪,954;赛欧,1044;比亚迪F3,1045;陆风X6,1053;雨燕,1054;开迪,1074;途胜";

    static {
        try {
            userForumColumns = DBUtils.getColumns("ac_user_forum");
        } catch (SQLException e) {
            log.error("get table columns error.", e);
        }
    }

    public static void main(String[] args) throws SQLException {
        AppUtils.initLog(InitUserForum.class);
        long tableX = -1;
        if (args != null && args.length > 0) {
            tableX = Long.parseLong(args[0]);
        }
        if (tableX < 0 || tableX > 10) {
            log.error("please input the tableX[0-9]");
            return;
        }
        long total = 0;
        if (tableX == 10) {
            for (int i = 0; i < 10; i++) {
                total += initUserForum(i);
            }
        } else {
            total = initUserForum(tableX);
        }
        log.info("insert into {} record", total);
    }

    private static int initUserForum(long tableX) throws SQLException {
        String sql = "select user_id,car_info,vip_info,city_id,province_id from ac_user where  user_id % 10 = " + tableX + " and (car_info!='' or province_id!=0)";
        Connection conn = DBUtils.getConnection();
        boolean isAutoCommit = false;
        PreparedStatement ps = null;
        int count = 0;
        try {
            isAutoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            List<JSONObject> userList = new ArrayList<JSONObject>(10000);
            ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                String carInfo = rs.getString("car_info");
                String vipInfo = rs.getString("vip_info");
                carInfo = carInfo == null ? "" : carInfo;
                vipInfo = vipInfo == null ? "" : vipInfo;
                //优化使用vipInfo
                String[] serialBrandInfo = vipInfo.length() > 0 ? vipInfo.split(";") : carInfo.split(";");
                long cityId = rs.getLong("city_id");
                long pId = rs.getLong("province_id");
                long serialId = 0;
                long uid = rs.getLong("user_id");
                if (serialBrandInfo.length == 4) {//只处理车系
                    try {
                        serialId = Long.parseLong(serialBrandInfo[2]);
                    } catch (java.lang.NumberFormatException e) {
                        log.error("dirty data 4:userId[{}],carInfo[{}],vipInfo[{}]", uid, carInfo, vipInfo);
                    }
                }
                if (serialBrandInfo.length == 2) {//正常应该是：品牌ID;品牌名称；但有些脏数据是：车系ID;车系名称；
                    String s = serialBrandInfo[0] + ";" + serialBrandInfo[1];
                    if (brandIdNameStr.contains(s)) {//品牌ID;品牌名称
                        continue;
                    } else if (specialSerialIdNameStr.contains(s)) {//车系ID;车系名称 （车系ID可能跟品牌ID相同的）
                        log.warn("dirty data 2 needToFix1:userId[{}],carInfo[{}],vipInfo[{}]", uid, carInfo, vipInfo);
                    } else {
                        log.warn("dirty data 2 needToFix2:userId[{}],carInfo[{}],vipInfo[{}]", uid, carInfo, vipInfo);
                    }
                    try {
                        serialId = Long.parseLong(serialBrandInfo[0]);
                    } catch (java.lang.NumberFormatException e) {
                        log.error("dirty data 2:userId[{}],carInfo[{}],vipInfo[{}]", uid, carInfo, vipInfo);
                    }
                }
                JSONObject obj = new JSONObject();
                obj.put("user_id", uid);
                obj.put("car_info", carInfo);
                obj.put("province_id", pId);
                obj.put("city_id", cityId);
                obj.put("serial_id", serialId);
                userList.add(obj);
            }
            for (JSONObject user : userList) {
                long uid = user.getLong("user_id");
                //添加版主俱乐部版块
                if (specialUid.contains(uid + ",")) {
                    count++;
                    user.put("status", 1);
                    user.put("create_date", new Date());
                    user.put("update_date", new Date());
                    user.put("forum_id", specialFid);
                    user.put("forum_name", specialFname);
                    addUserForum(conn, count, user);
                }
                //查关联的论坛
                JSONArray fs = getForums(conn, user);
                if (fs.isEmpty()) {
                    continue;
                }
                //添加车友圈关系
                count++;
                user.put("status", 1);
                user.put("create_date", new Date());
                user.put("update_date", new Date());
                user.put("forum_id", fs.getJSONObject(0).getLong("forum_id"));
                user.put("forum_name", fs.getJSONObject(0).getString("forum_name"));
                addUserForum(conn, count, user);
                if (fs.size() == 1) {
                    continue;
                }
                //添加车友圈关系
                count++;
                user.put("status", 1);
                user.put("create_date", new Date());
                user.put("update_date", new Date());
                user.put("forum_id", fs.getJSONObject(1).getLong("forum_id"));
                user.put("forum_name", fs.getJSONObject(1).getString("forum_name"));
                addUserForum(conn, count, user);
            }
            conn.commit();
            return count;
        } catch (SQLException ex) {
            if (conn != null) {
                conn.rollback();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(isAutoCommit);
                } catch (SQLException ex) {
                    log.error("setAutoCommit fail!", ex);
                }
            }
            DBUtils.releaseConn(conn, ps, null);
        }
        return count;
    }

    private static JSONArray getForums(Connection conn, JSONObject user) throws SQLException {
        JSONArray arr = new JSONArray();
        long fid = 0;
        String fname = "";
        String sql;
        if (user.getLongValue("city_id") == 440100 || user.getLongValue("city_id") == 440300) {
            sql = "select forum_id,forum_name from tmp_area2forum where area_id = " + user.getLongValue("city_id");
            ResultSet rs = conn.prepareStatement(sql).executeQuery();
            if (rs.next()) {
                fid = rs.getLong("forum_id");
                fname = rs.getString("forum_name");
            }
            rs.close();
        } else if (user.getLongValue("province_id") > 0) {
            sql = "select forum_id,forum_name from tmp_area2forum where area_id = " + user.getLongValue("province_id");
            ResultSet rs = conn.prepareStatement(sql).executeQuery();
            if (rs.next()) {
                fid = rs.getLong("forum_id");
                fname = rs.getString("forum_name");
            }
            rs.close();
        }
        if (fid > 0 && fname.length() > 0) {
            JSONObject obj = new JSONObject();
            obj.put("forum_id", fid);
            obj.put("forum_name", fname);
            arr.add(obj);
        }
        if (user.getLongValue("serial_id") > 0) {
            sql = "select forum_id,forum_name from tmp_serial2forum where serial_id = " + user.getLongValue("serial_id");
            ResultSet rs = conn.prepareStatement(sql).executeQuery();
            if (rs.next()) {
                fid = rs.getLong("forum_id");
                fname = rs.getString("forum_name");
                JSONObject obj = new JSONObject();
                obj.put("forum_id", fid);
                obj.put("forum_name", fname);
                arr.add(obj);
            }
            rs.close();
        }
        return arr;
    }

    private static int addUserForum(Connection conn, int count, JSONObject user) throws SQLException {
        long userId = user.getLongValue("user_id");
        long forumId = user.getLongValue("forum_id");
        if (userId <= 0 || forumId <= 0) {
            log.info("the userId:{} forumId:{} not exists", userId, forumId);
            return 0;
        }
        String sql = DBUtils.getInsertIgnoreIntoSql("ac_user_forum_", userId % 10, userForumColumns);
        log.debug("addUserForum\n sql: {}, \n data: {}", sql, user);
        PreparedStatement ps = conn.prepareStatement(sql);
        int idx = 1;
        for (String col : userForumColumns) {
            ps.setObject(idx++, user.get(col));
        }
        ps.executeUpdate();
        log.info("add {} userForum-{}-{}", count, user.get("user_id"), user.get("forum_id"));
        return 1;
    }
}
